package model;

import java.sql.*;
import java.util.List;

import org.apache.commons.fileupload.FileItem;

import myBeans.BookInfo;
import myBeans.TopViewinfo;


public class BookRespository {
    private Connection conn;
    
	public BookRespository(Connection conn){
		this.conn = conn;
	}
	
	public boolean DeleteBookType(String BookTypeID)
	{
		String sql = "Delete From TBBookType Where BTypeID = ?";
		
		try {
			PreparedStatement preStm = conn.prepareStatement(sql);
			preStm.setString(1, BookTypeID);
			int rowEffect = preStm.executeUpdate();
			
			if(rowEffect > 0){
				return true;				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return false;
	}
	
	public BookInfo GetBookById(String isbn){
		BookInfo book = null;
		String sql = "Select BISBN, BNameThai, BNameEng, BWriter, BPublishing, BEdition, BTranslate, BPrice, BTypeID, BCountPage,BCoverImage,BContentType " +
				" From tbBooks" +
				" Where bisbn = ?";
		
		try {
			PreparedStatement preStm = conn.prepareStatement(sql);
			preStm.setString(1, isbn);
			ResultSet rs = preStm.executeQuery();
			
			if(rs.next()){
				book = new BookInfo();
				
				book.setIsbn(isbn);
				book.setBookNameThai(rs.getString("BNameThai"));
				book.setBookCoverImage(rs.getBlob("BCoverImage"));
				book.setCoverImageContentType(rs.getString("BContentType"));
				
			}
			
			return book;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return book;
	}
	
	public boolean UpdateBookCoverImage(String isbn, FileItem coverImage)
	{
		if(coverImage == null) return false;
		
		String contentType = "";
		byte[] attachfile = null;
		
		contentType = coverImage.getContentType();
		attachfile = coverImage.get();
	
		String sql = "update tbbooks" +
				" set bcoverimage=?,bcontenttype=?" +
				" where bisbn=?";
		
		try {
			//Statement stm = conn.createStatement();
			PreparedStatement preStm = conn.prepareStatement(sql);
			
			preStm.setBytes(1, attachfile);
			preStm.setString(2, contentType);
			preStm.setString(3, isbn);
			
			int result = preStm.executeUpdate();
			 
			if(result > 0)
				return true;
			else
				return false;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		
		
		return false;
	}
	
	
	public boolean AddBook(BookInfo book)
	{
		String sql;
		
		if(book.getOldISBN().isEmpty()){
			sql = "Insert into tbBooks(BISBN, BNameThai, BNameEng, BWriter, BPublishing, BEdition, BTranslate, BPrice, BTypeID, BCountPage)" +
					" Values(?,?,?,?,?,?,?,?,?,?)";	
		}else{
			sql = "update 	tbBooks" +
					" set BISBN=?," +
					" BNameThai=?," +
					" BNameEng=?," +
					" BWriter=?, " +
					" BPublishing=?, " +
					" BEdition=?, " +
					" BTranslate=?, " +
					" BPrice=?, " +
					" BTypeID=?, " +
					" BCountPage=?" +
					" Where BISBN='" + book.getIsbn() + "'";
					
		}
		
		
		try {
			PreparedStatement prepStm = conn.prepareStatement(sql);
			
			prepStm.setString(1, book.getIsbn());				//BISBN
			prepStm.setString(2, book.getBookNameThai());		//BNameThai
			prepStm.setString(3, book.getBookNameEng());		//BNameEng
			prepStm.setString(4, book.getAuthor());				//BWriter
			prepStm.setString(5, book.getPublisher());			//BPublishing, 
			prepStm.setInt(6, book.getPublishNo());				//BEdition, 
			prepStm.setString(7, book.getTranslator());			//BTranslate, 
			prepStm.setInt(8, book.getPrice());					//BPrice, 
			prepStm.setString(9, book.getType());				//BTypeID, 
			prepStm.setInt(10, book.getTotalPage());			//BCountPage
			
			
			int result = prepStm.executeUpdate();
			
			
			if(result > 0)
				return true;
			else
				return false;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		
		
		return false;
	}

	public TopViewinfo GetTopViewinfo() {
		
		TopViewinfo view = new TopViewinfo();
		
		try
				
		{
			Statement stmt = conn.createStatement();
			String sql = "SELECT SBID, SBName,SBCountView,BISBN FROM TBShareBook";
					//sql += " Where UserID='" + sID + "'";
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if(rs.next()){	
				
				//view.setSID(rs.getString("SBID"));
				//view.setSName(rs.getString("SBName"));
				
				//System.out.print(view.getSID());
							
				
			}
			
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		
		return view;
	}
}
	